package com.uottawa.library.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.text.BoxView;

import com.uottawa.library.domain.Item;

public class ItemDAO extends BaseDAO {

	public boolean addItem(Item item) {
		String query = "INSERT INTO ITEM (ISBN) VALUES (" + item.getIsbn() + ")"; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return true;
	}

	public boolean RemoveItem(int id) {
		String query = "DELETE FROM ITEM WHERE ITEM_ID =" + id; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return true;
	}

	public List<Item> getAllItems() {
		List<Item> result = new ArrayList<Item>();
		
		String query = "SELECT * FROM ITEM" ; 
		Connection connection = getConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(query);
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				while(rs.next()) {
					Item item = new Item();
					item.setItemId(rs.getInt("ITEM_ID"));
					item.setIsbn(rs.getInt("ISBN"));
					result.add(item);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return result;
	}

	public boolean updateItem(int id, int isbn) {
		String query = "UPDATE  ITEM SET ISBN='" + isbn + "' WHERE ITEM_ID = " + id; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return true;
	}

	public List<Item> getAllItems(int isbnInt) {
		List<Item> result = new ArrayList<Item>();
		
		String query = "SELECT * FROM ITEM WHERE ISBN = " + isbnInt ; 
		Connection connection = getConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(query);
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				while(rs.next()) {
					Item item = new Item();
					item.setItemId(rs.getInt("ITEM_ID"));
					item.setIsbn(rs.getInt("ISBN"));
					result.add(item);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;	
	}

	public List<Item> getAllAvailableItems(int isbnInt) {
List<Item> result = new ArrayList<Item>();
		
		String query = "SELECT * FROM ITEM WHERE ISBN = " + isbnInt + " AND " +
				"ITEM_ID NOT IN ( SELECT ITEM_ID FROM LOAN WHERE RETURNED = 0)" ; 
		Connection connection = getConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(query);
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				while(rs.next()) {
					Item item = new Item();
					item.setItemId(rs.getInt("ITEM_ID"));
					item.setIsbn(rs.getInt("ISBN"));
					result.add(item);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;	
	}
	
	public boolean isBorrowed(int itemId) {
		boolean borrowed = false;
				String query = "SELECT * FROM LOAN WHERE ITEM_ID = " + itemId + " AND RETURNED = 0" ; 
				Connection connection = getConnection();
				try {
					PreparedStatement stmt = connection.prepareStatement(query);
					ResultSet rs = stmt.executeQuery(query);
					if(rs !=null)
					{
						if(rs.next()) {
							borrowed = true;
						}
					}
					
					
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return borrowed;	
			}
	
	
//	public Boolean checkIfAvailable(int itemId){
//		String query = "SELECT * FROM ITEM WHERE ITEM_ID = " + itemId + " AND " +
//				"ITEM_ID NOT IN ( SELECT ITEM_ID FROM LOAN WHERE RETURNED = 0)" ; 
//		Connection connection = getConnection();
//		try {
//			PreparedStatement stmt = connection.prepareStatement(query);
//			ResultSet rs = stmt.executeQuery(query);
//			if(rs !=null)
//			{
//
//				return true;
//			}
//			
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		return false;	
//	}
	
}
